/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.report.model;

import com.tamnhat.report.entity.ReportP1;
import com.tamnhat.report.entity.SUM_SDP_CDR;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class SUM_SDP_CDRModel {

    public static ArrayList<SUM_SDP_CDR> getAllByConditionByDay(String tuNgay, String denNgay,String productId,String serviceId,String partnerId) throws Exception{
        ArrayList<SUM_SDP_CDR> list = new ArrayList<SUM_SDP_CDR>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = AppServer.getConnection();
            st = conn.createStatement();
            String strSQL = "";
            strSQL =  " select a.date1, b.product_name, c.service_name, a.charge_count,a.sum_charge_amount,d.partner_name"
                    + " from (select to_char(a.sum_date,'dd-mm-yyyy') date1,a.product_id,a.service_id,a.charge_count,a.sum_charge_amount,a.partner_id"
                    + " from sum_sdp_cdr a"
                    + " where sum_date >= to_date('"+tuNgay+"','dd-mm-yyyy') and sum_date < to_date('"+denNgay+"','dd-mm-yyyy') + 1"                    
                    + " and (a.product_id = "+productId+" or "+productId+" is null) "
                    + " and (a.service_id = "+serviceId+" or "+serviceId+" is null) "
                    + " and (a.partner_id = "+partnerId+" or "+partnerId+" is null) "
                    + " group by a.partner_id"
                    + " order by a.sum_date) a, sdp_products b,sdp_services c,partners d"
                    + " where (a.product_id = b.product_id(+)) and (a.service_id = c.service_id(+)) and a.partner_id = d.partner_id"
                    + " order by a.date1,c.service_name,b.product_name";
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                SUM_SDP_CDR s = new SUM_SDP_CDR();
                s.setSum_date(rs.getString(1));
                s.setProduct_name(rs.getString(2));
                s.setService_name(rs.getString(3));
                s.setCharge_count(Long.parseLong(rs.getString(4)));
                s.setSum_charge_amount(Long.parseLong(rs.getString(5)));
                s.setPartner_name(rs.getString(6));
                list.add(s);
            }            
            return list;
        } finally{
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(conn);
        }
    }
    public static ArrayList<SUM_SDP_CDR> getAllByConditionNoDay(String tuNgay, String denNgay,String productId,String serviceId,String partnerId) throws Exception{
        ArrayList<SUM_SDP_CDR> list = new ArrayList<SUM_SDP_CDR>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = AppServer.getConnection();
            st = conn.createStatement();
            String strSQL = "";
           strSQL = " select b.product_name, c.service_name, a.sum1, a.sum2,d.partner_name from (select a.product_id,a.service_id,sum(a.charge_count) as sum1,sum(a.sum_charge_amount) as sum2,a.partner_id"
                    + " from sum_sdp_cdr a "
                    + " where a.sum_date >= to_date('"+tuNgay+"','dd-mm-yyyy') and a.sum_date < to_date('"+denNgay+"','dd-mm-yyyy') + 1 "                    
                    + " and (a.product_id = "+productId+" or "+productId+" is null) "
                    + " and (a.service_id = "+serviceId+" or "+serviceId+" is null)"
                    + " and (a.partner_id = "+partnerId+" or "+partnerId+" is null) "
                    + " group by a.product_id,a.service_id,a.partner_id"
                    + " order by a.product_id,a.service_id) a, sdp_products b,sdp_services c ,partners d"
                    + " where (a.product_id = b.product_id(+)) and (a.service_id = c.service_id(+)) and a.partner_id = d.partner_id"
                    + " order by b.product_name, c.service_name";
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                SUM_SDP_CDR s = new SUM_SDP_CDR();
                s.setSum_date(tuNgay+ "->" +denNgay);
                s.setProduct_name(rs.getString(1));
                s.setService_name(rs.getString(2));
                s.setCharge_count(Long.parseLong(rs.getString(3)));
                s.setSum_charge_amount(Long.parseLong(rs.getString(4)));
                s.setPartner_name(rs.getString(5));
                list.add(s);
            }            
            return list;
        } finally{
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(conn);
        }
    }
    
    public static String getPartnerIdByUserId(long userId) throws Exception{
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = AppServer.getConnection();
            st = conn.createStatement();
            String strSQL = " ";            
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                
            }            
        } finally{
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(conn);
        }
        return "";
    }
}
